package com.isky.visual.db.factory;

import com.isky.visual.constant.DbConstant;
import com.isky.visual.db.domain.DatabaseConfig;
import com.isky.visual.db.entity.Source;
import com.isky.visual.db.enums.DBType;
import com.isky.visual.exception.GlobalException;
import com.isky.visual.result.CodeMsg;
import com.isky.visual.util.DBUtil;
import org.apache.commons.lang.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.*;

/**
 * @version v1.0
 * @ProjectName visual_parent
 * @ClassName SqlServerManager
 * @Description sqlService的 相关管理操作类
 * @Author wudl
 * @Date DATE{TIME}
 */

public class SqlServerManager implements IDbManager {
    private Logger log = LoggerFactory.getLogger(MySqlManager.class);
    private Source source;

    public SqlServerManager(Source source) {
        this.source = source;
    }

    @Override
    public Connection getConnection() {
        Connection connection = null;
        try {
            //加载数据驱动
            Class.forName(DBType.SqlServer.getDriverClass()).newInstance();
            String url = String.format(DBType.SqlServer.getConnectionUrlPattern(),
                    source.getDbUrl(),
                    source.getListenPort(),
                    source.getDbName());
            connection = DriverManager.getConnection(url,source.getUserName(),source.getUserPwd());
        } catch (Exception e) {
            e.printStackTrace();
            log.error("数据库获取连接异常" + e.getMessage());
            throw new GlobalException(CodeMsg.DB_CONNECTION_ERROR);
        }
        return connection;
    }

    @Override
    public String getShowColumnSql(String tableName, Source source){
        String sql = "select \n" +
                " t1.column_name,\n" +
                " t1.column_comment\n" +
                " from\n" +
                " (SELECT\n" +
                " CONVERT(nvarchar(50),ISNULL(B.[name], '')) as column_name,\n" +
                " CONVERT(nvarchar(50),ISNULL(C.[value], '')) as column_comment\n" +
                " FROM sys.tables A\n" +
                " INNER JOIN sys.columns B \n" +
                " ON B.object_id = A.object_id\n" +
                " LEFT JOIN sys.extended_properties C \n" +
                " ON C.major_id = B.object_id \n" +
                " AND C.minor_id = B.column_id\n" +
                " WHERE A.name = '"+tableName+"')t1 \n" +
                " left join \n" +
                " (select \n" +
                " column_name,\n" +
                " data_type as column_type\n" +
                " from information_schema.columns t \n" +
                " where t.table_name='"+tableName+"') t2\n" +
                " on t1.column_name = t2.column_name";
        return sql;
    }

    @Override
    public String getShowTableSql(){
        String sql = "select t1.table_name,t1.table_type,t2.table_remark from \n" +
                "(select\n" +
                "sys.name as table_name,\n" +
                "(CASE sys.xtype \n" +
                "WHEN 'U' THEN 'TABLE' \n" +
                "ELSE 'VIEW' END)as table_type\n" +
                "from sysobjects sys where sys.xtype in ('U','V')) t1 left join\n" +
                "(\n" +
                "SELECT CONVERT(nvarchar(50),ISNULL(A.[name], '')) as table_name,\n" +
                "CONVERT(nvarchar(50),ISNULL(C.[value], '')) as table_remark\n" +
                "FROM sys.tables A LEFT JOIN \n" +
                "sys.extended_properties C ON C.major_id = A.object_id\n" +
                "WHERE C.minor_id=0\n" +
                ") t2 on t1.table_name = t2.table_name";
        return sql;
    }

    @Override
    public String getShowTableSql(Source source) {
        return "SELECT name FROM "+source.getDbName()+"..sysobjects Where xtype='U' ORDER BY name";
    }

    /**
     * 根据链接和sql 获取sqlServer库中对应表字段信息
     * @param con
     * @param sql
     * @return
     * @throws Exception
     */
    @Override
    public List<Map<String, Object>> getTableColumn(Connection con, String sql) throws Exception {
        List<Map<String, Object>> queryList = new ArrayList<>();
        Statement statement = null;
        ResultSet resultSet = null;
        if (null == con || StringUtils.isBlank(sql)) {
            return null;
        }
        try{
            statement = con.createStatement();
            resultSet = statement.executeQuery(sql);
            while(resultSet.next()){
                Map<String, Object> map = new HashMap<>(16);
                map.put("COLUMN_NAME", resultSet.getString("column_name"));
                map.put("namecn", resultSet.getString("column_comment"));
                queryList.add(map);
            }
        }catch(Exception e){
            log.error(e.getMessage());
            throw new GlobalException(CodeMsg.DB_QUERY_ERROR);
        } finally {
            DBUtil.closeLink(null,statement,resultSet,con);
        }
        return queryList;
    }

    @Override
    public String getSingleTableCountSql(String tableName) {
        return "SELECT a.name, b.rows as COUNT" +
                " FROM sysobjects AS a INNER JOIN sysindexes AS b ON a.id = b.id" +
                " WHERE (a.type = 'u') AND (b.indid IN (0, 1))" +
                " ORDER BY a.name,b.rows DESC";
    }

    @Override
    public String getSingleTableDataSql(String tableName, Integer limit) {
        return "select top "+ limit +" *  from " + tableName;
    }
}
